package com.example.dao.impl;

import com.example.dao.UserDao;
import com.example.pojo.User;
import com.example.service.impl.UserServiceImpl;
import com.example.utils.DBConnection;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("userDao")
public class UserDaoImpl implements UserDao {
    static String driver = "com.mysql.cj.jdbc.Driver";
    static String url = "jdbc:mysql://localhost:3306/bbcforum";
    static String userName = "root";
    static String passWord = "gy27979551142";
    public User login(String username,String password) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Class.forName(driver);
        conn = DriverManager.getConnection(url,userName,passWord);
        String sql = "select u_id uId,username,password,nickname,name,sex,avatar,phone,id_number idNumber," +
                "enter_year " +
                "enterYear,class_name className,subject,email,message_count messageCount,topic_count topicCount," +
                "comment_count commentCount" +
                " from user where username =" +
                " ? ";
        ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        rs = ps.executeQuery();
        User user = null;
        while(rs.next()) {
            String word = rs.getString("password");
            if(!word.equals(password)) {
                return user;
            }
            Integer uId = rs.getInt("uId");
            String nickname = rs.getString("nickname");
            String name = rs.getString("name");
            String sex = rs.getString("sex");
            String avatar = rs.getString("avatar");
            String phone = rs.getString("phone");
            String idNumber = rs.getString("idNumber");
            String enterYear = rs.getString("enterYear");
            String className = rs.getString("className");
            String subject = rs.getString("subject");
            String email = rs.getString("email");
            Integer messageCount = rs.getInt("messageCount");
            Integer topicCount = rs.getInt("topicCount");
            Integer commentCount = rs.getInt("commentCount");
            user = new User(uId,username,password,nickname,name,sex,avatar,phone,idNumber,enterYear,className,
                    subject,email,messageCount,topicCount,commentCount);
        }
        rs.close();
        ps.close();
        conn.close();
        return user;
    }

    public List<Map<String,Object>> getUserNewsList(Integer uId) {
        Connection conn = null;
        PreparedStatement ps1 = null;
        ResultSet rs1 = null;
        PreparedStatement ps2 = null;
        ResultSet rs2 = null;
        List<Map<String,Object>> userNewsList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select topic.t_id tId,title,comment.u_id uId,comment.publish_time publishTime from user," +
                    "topic,comment " +
                    "where user.u_id = ? and user.u_id = topic.u_id and topic.t_id = comment.t_id;";

            ps1 = conn.prepareStatement(sql);
            ps1.setInt(1,uId);
            rs1 = ps1.executeQuery();

            while(rs1.next()) {
                Map<String,Object> map = new HashMap<>();
                Integer comUId = rs1.getInt("uId");
                sql = "select nickname from user where u_id = ?";
                ps2 = conn.prepareStatement(sql);
                ps2.setInt(1,comUId);
                rs2 = ps2.executeQuery();
                while(rs2.next()) {
                    map.put("nickname",rs2.getString("nickname"));
                }
                map.put("uId",comUId);
                map.put("tId",rs1.getInt("tId"));
                map.put("title",rs1.getString("title"));
                map.put("uId",rs1.getInt("uId"));
                map.put("publishTime",rs1.getString("publishTime").substring(0,16));
                userNewsList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs1,ps1,conn);
        DBConnection.close(rs2,ps2,conn);
        return userNewsList;
    }

    public List<Map<String,Object>> getUserTopicList(Integer uId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> userTopicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select real_status realStatus,type,t_id tId,check_status checkStatus,title,publish_time " +
                    "publishTime,likes,collection_times collectionTimes from topic,user " +
                    "where user.u_id = ? and user.u_id = topic.u_id";

            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("tId",rs.getInt("tId"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                map.put("checkStatus",rs.getString("checkStatus"));
                userTopicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return userTopicList;
    }

    public List<Map<String,Object>> getUserCollectionList(Integer uId) {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        List<Map<String,Object>> userCollectionList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select distinct type,topic.t_id tId,real_status realStatus,title,collection.publish_time " +
                    "publishTime,likes,collection_times collectionTimes from topic,collection,user " +
                    "where user.u_id = ? and user.u_id = collection.u_id and collection.t_id = topic.t_id;";

            ps1 = conn.prepareStatement(sql);
            ps1.setInt(1,uId);
            rs1 = ps1.executeQuery();

            while(rs1.next()) {
                Map<String,Object> map = new HashMap<>();
                Integer tId = rs1.getInt("tId");
                sql = "select nickname from topic,user where topic.t_id = ? and topic.u_id = user.u_id";
                ps2 = conn.prepareStatement(sql);
                ps2.setInt(1,tId);
                rs2 = ps2.executeQuery();
                while(rs2.next()) {
                    map.put("nickname", rs2.getString("nickname"));
                }
                map.put("tId",tId);
                map.put("type",rs1.getString("type"));
                map.put("title",rs1.getString("title"));
                map.put("publishTime",rs1.getString("publishTime").substring(0,16));
                map.put("likes",rs1.getInt("likes"));
                map.put("collectionTimes",rs1.getInt("collectionTimes"));
                map.put("realStatus",rs1.getString("realStatus"));
                userCollectionList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs1,ps1,conn);
        DBConnection.close(rs2,ps2,conn);
        return userCollectionList;
    }

//    public List<Map<String,Object>> getUserNewsList(Integer uId) {
//        Connection conn = null;
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        List<Map<String,Object>> userNewsList = new ArrayList<>();
//        try {
//            conn = DBConnection.getConnection();
//            String sql = "select t_id tId,title,type,publish_time publishTime from user,topic,collection where user" +
//                    ".u_id = ? " +
//                    "and" +
//                    " topic.t_id = " +
//                    "collection.t_id";
//
//            ps = conn.prepareStatement(sql);
//            ps.setInt(1,uId);
//            rs = ps.executeQuery();
//
//            while(rs.next()) {
//                Map<String,Object> map = new HashMap<>();
//                map.put("tId",rs.getInt("tId"));
//                map.put("type",rs.getString("type"));
//                map.put("title",rs.getString("title"));
//                map.put("publishTime",rs.getString("publishTime").substring(0,16));
//                map.put("likes",rs.getInt("likes"));
//                map.put("collectionTimes",rs.getInt("collectionTimes"));
//                map.put("realStatus",rs.getString("realStatus"));
//                map.put("checkStatus",rs.getString("checkStatus"));
//                userNewsList.add(map);
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        DBConnection.close(rs,ps,conn);
//        return userNewsList;
//    }

    public Map<String,Object> getTopic(Integer tId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,Object> topicMap = new HashMap<>();
        try {
            conn = DBConnection.getConnection();
            String sql = null;

            sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                    "likes," +
                    "collection_times collectionTimes,real_status realStatus from topic,user where topic.t_id = ? " +
                    "and topic.u_id = " +
                    "user.u_id";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,tId);
            rs = ps.executeQuery();

            while(rs.next()) {
                topicMap.put("nickname",rs.getString("nickname"));
                topicMap.put("tId",rs.getInt("tId"));
                topicMap.put("uId",rs.getInt("uId"));
                topicMap.put("avatar",rs.getString("avatar"));
                topicMap.put("type",rs.getString("type"));
                topicMap.put("title",rs.getString("title"));
                topicMap.put("content",rs.getString("content"));
                topicMap.put("publishTime",rs.getString("publishTime").substring(0,16));
                topicMap.put("likes",rs.getInt("likes"));
                topicMap.put("collectionTimes",rs.getInt("collectionTimes"));
                topicMap.put("realStatus",rs.getString("realStatus"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return topicMap;
    }

    public int updatePass(Integer uId,String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            String sql = "update user set password = ? where u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,password);
            ps.setInt(2,uId);
            int count = ps.executeUpdate();
            return count;

        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return -1;
    }

    //更新用户头像
    public Integer updateAvatar(Integer uId,String fileName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            String sql = "update user set avatar = ? where u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,fileName);
            ps.setInt(2,uId);
            int count = ps.executeUpdate();
            return count;

        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return -1;
    }

    //修改用户信息
    public Integer updateInfo(Integer uId,String nickname,String phone,String email) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "update user set nickname = ?,phone = ?,email = ? where u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,nickname);
            ps.setString(2,phone);
            ps.setString(3,email);
            ps.setInt(4,uId);
            count = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Map<String,Object> getOtherUser(Integer uId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,Object> otherUserMap = new HashMap<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select username,nickname,name,sex,avatar,email,enter_year enterYear," +
                    "topic_count topicCount,comment_count commentCount from user where u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            rs = ps.executeQuery();
            while(rs.next()) {
                otherUserMap.put("username",rs.getString("username"));
                otherUserMap.put("nickname",rs.getString("nickname"));
                otherUserMap.put("name",rs.getString("name"));
                otherUserMap.put("sex",rs.getString("sex"));
                otherUserMap.put("avatar",rs.getString("avatar"));
                otherUserMap.put("enterYear",rs.getString("enterYear"));
                otherUserMap.put("email",rs.getString("email"));
                otherUserMap.put("topicCount",rs.getInt("topicCount"));
                otherUserMap.put("commentCount",rs.getInt("commentCount"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return otherUserMap;
    }

}
